package core.db;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;


/**
 * DB工具类
 * @author lost
 *
 */
public class DBTool {
	private static Logger logger = Logger.getRootLogger() ;
	public static void update(String sql){
		DBManager db = DBManagerFactory.getInctence().getManager() ;  
		try {
			db.open() ;
			logger.info("sql:"+sql) ;
			db.excuteUpdate(sql) ;
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally{
			try {
				db.close() ;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
	}
	/**
	 * 同getResults，不同的是只返回一条记录
	 * @param originDto
	 * @param sql
	 * @return
	 */
	public static <T> T getResult(T originDto , String sql){
		DBManager db = DBManagerFactory.getInctence().getManager() ;  
		try {
			db.open() ;
			logger.info("sql:"+sql) ;
			ResultSet rs = db.excuteQuery(sql) ;
			
			ResultSetMetaData rsmd = rs.getMetaData() ;

			Set<String> columns = getColumnsNameSet(rsmd);
			Set<Method> properties = getProperties(originDto.getClass()) ;
			Set<Method> setMethods = getMethods(columns , properties) ;
			
			if(rs.next()){
				T t = (T)fillDto(originDto.getClass().newInstance() , setMethods , rs) ;
				return t ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally{
			try {
				db.close() ;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	/**
	 * 这个方法传入要返回对象类型的其中一个对象和SQL，
	 * 可以返回结果集
	 * 注意，这个方法是用反射来实现的，所以如果追求效率
	 * 则不要使用
	 * @param originDto
	 * @param sql
	 * @return
	 */
	public static <T> List<T> getResults(T originDto , String sql){
		List<T> res  = new ArrayList<T>() ;
		DBManager db = DBManagerFactory.getInctence().getManager() ;  
		try {
			db.open() ;
			logger.info("sql:"+sql) ;
			ResultSet rs = db.excuteQuery(sql) ;
			
			ResultSetMetaData rsmd = rs.getMetaData() ;

			Set<String> columns = getColumnsNameSet(rsmd);
			Set<Method> properties = getProperties(originDto.getClass()) ;
			Set<Method> setMethods = getMethods(columns , properties) ;
			
			while(rs.next()){
				T t = (T)fillDto(originDto.getClass().newInstance() , setMethods , rs) ;
				res.add(t) ;
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		} finally{
			try {
				db.close() ;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return res;
	}
	
	/**
	 * 填充DTO对象
	 * @param object
	 * @param setMethods
	 * @param rs
	 * @return
	 * @throws Exception 
	 */
	private static Object fillDto(Object object, Set<Method> setMethods,
			ResultSet rs) throws Exception {
		Iterator<Method> it = setMethods.iterator();
		while(it.hasNext()){
			Method m = it.next() ;
			String name = m.getName().replaceFirst("set", "") ;
			m.invoke(object, rs.getString(name)) ;
		}
		return object;
	}

	/**
	 * 根据现有的列和数据集的列返回要执行的设置方法
	 * @param columns
	 * @param properties
	 * @return
	 */
	private static Set<Method> getMethods(Set<String> columns,
			Set<Method> properties) {
		Set<Method> res = new HashSet<Method>() ;
		Iterator<String> it = columns.iterator() ;
		while(it.hasNext()){
			String name = it.next() ;
			Iterator<Method> methodIt = properties.iterator() ;
			while(methodIt.hasNext()){
				Method m = methodIt.next() ;
				if(m.getName().replaceFirst("set", "").equalsIgnoreCase(name)){
					res.add(m) ;
				}
			}
		}//end columnloop ;
		return res;
	}

	/**
	 * 获取类的可设置的属性名集合
	 * @param class1
	 * @return
	 */
	private static Set<Method> getProperties(Class classModel) {
		Method[] methods = classModel.getMethods() ;
		Set<Method> res = new HashSet<Method>() ;
		for(int i = 0 ; i < methods.length ;i ++){
			String name = methods[i].getName() ;
			if(name.startsWith("set"))//可设置的
				res.add(methods[i]) ;
		}
		return res;
	}

	/**
	 * 获取数据集的列名集合
	 * @param rsmd
	 * @return
	 * @throws SQLException
	 */
	private static Set<String> getColumnsNameSet(ResultSetMetaData rsmd)
			throws SQLException {
		Set<String> columns = new HashSet<String>() ;
		int columnSize = rsmd.getColumnCount() ;
		for(int i = 1 ; i <= columnSize ;i ++){
			columns.add(rsmd.getColumnName(i).toUpperCase()) ;
		}
		return columns ;
	}
}
